/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.vce.election;

import com.vce.User;
import com.vce.Year;
import com.vce.election.domain.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Expression;
import org.springframework.orm.hibernate3.HibernateCallback;

/**
 *
 * @author Ggutierrez
 */
public class ElectionDao extends org.springframework.orm.hibernate3.support.HibernateDaoSupport {

    private int maxResults;
    private DataSource dataSource;

    public ElectionDao() {
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setMaxResults(int maxResults) {
        this.maxResults = maxResults;
    }

    public Object unique(List results) {
        return results == null || results.isEmpty() ? null : results.get(0);
    }

    private boolean isBlank(String value) {
        return value == null || value.trim().length() == 0;
    }

    public void attach(Object object) {
        getHibernateTemplate().refresh(object);
    }

    public Year getYear(Long id) {
        return (Year) getHibernateTemplate().get(Year.class, id);
    }

    public User getUser(Long id) {
        String hql = "from User this left join fetch this.assignedTowns towns where this.id = ?";
        List<User> list = getHibernateTemplate().find(hql, id);
        return list == null || list.isEmpty() || list.get(0) == null ? null : list.get(0);
    }

    public ElectionType getElectionType(Long id) {
        return (ElectionType) getHibernateTemplate().get(ElectionType.class, id);
    }

    public ElectionType getElectionType(boolean current) {
        List results = getHibernateTemplate().find("from ElectionType this where this.current = ?", current);
        return results != null && !results.isEmpty() ? (ElectionType) results.get(0) : null;
    }
    
    public ElectionType getElectionType(String description) {    
        List args = new ArrayList();

        StringBuilder hql = new StringBuilder("from ElectionType this join fetch this.year year where year.active = true ");

        if (!isBlank(description)) {
            hql.append(" and upper(this.description) = ? ");
            args.add(description.trim().toUpperCase());
        }
        List results = find(hql.toString(), args.toArray(), -1);
        
        return results != null && !results.isEmpty() ? (ElectionType) results.get(0) : null;
    }

    public Party getParty(Long id) {
        return (Party) getHibernateTemplate().get(Party.class, id);
    }

    public Party getParty(Integer stall, ElectionType electionType) {
        String hql = "from Party this join fetch this.electionType electionType where this.stall = ? and electionType.id = ? ";
        List results = getHibernateTemplate().find(hql, new Object[]{stall, electionType.getId()});
        return results != null && !results.isEmpty() ? (Party) results.get(0) : null;
    }

    public Candidate getCandidate(Long id) {
        return (Candidate) getHibernateTemplate().get(Candidate.class, id);
    }

    public Position getPosition(Long id) {
        return (Position) getHibernateTemplate().get(Position.class, id);
    }

    public State getState(Long id) {
        return (State) getHibernateTemplate().get(State.class, id);
    }

    public Town getTown(Long id) {
        String hql = "from Town this where this.id = ?";
        return (Town) unique(getHibernateTemplate().find(hql, id));
    }

    public Town getTown(String name) {
        String hql = "from Town this where this.name = ?";
        return (Town) unique(getHibernateTemplate().find(hql, name));
    }

    public BallotCentre getBallotCentre(Long id) {
        String hql = "from BallotCentre this inner join fetch this.town town where this.id = ?";
        return (BallotCentre) unique(getHibernateTemplate().find(hql, id));
    }

    public BallotCentre getBallotCentre(String description) {
        String hql = "from BallotCentre this inner join fetch this.town town where this.description = ?";
        return (BallotCentre) unique(getHibernateTemplate().find(hql, description));
    }

    public Jrv getJrv(Long id) {
        String hql = "from Jrv this inner join fetch this.ballotCentre ballot inner join fetch ballot.town town where this.id = ?";
        return (Jrv) unique(getHibernateTemplate().find(hql, id));
    }

    public Document getDocument(Long id) {
        return (Document) getHibernateTemplate().get(Document.class, id);
    }

    public Vote getVote(Long id) {
        return (Vote) getHibernateTemplate().get(Vote.class, id);
    }

    public Fiscal getFiscal(Long id) {
        return (Fiscal) getHibernateTemplate().get(Fiscal.class, id);
    }

    public Impugnment getImpugnement(Long id) {
        return (Impugnment) getHibernateTemplate().get(Impugnment.class, id);
    }

    public List<Year> findYears() {
        return (List<Year>) getHibernateTemplate().find("from Year this");
    }

    public List<User> findUsers() {
        return (List<User>) getHibernateTemplate().find("from User this");
    }

    public User findUserByLogin(String login) {
        List results = getHibernateTemplate().find("from User user where user.login = ?", login);
        return results != null && !results.isEmpty() ? (User) results.get(0) : null;
    }

    public List<ElectionType> findElectionTypes() {
        return (List<ElectionType>) getHibernateTemplate().find("from ElectionType this");
    }

    public List<ElectionType> findElectionTypes(Year year) {
        DetachedCriteria criteria = DetachedCriteria.forClass(ElectionType.class);
        criteria.add(Expression.eq("year", year));
        return (List<ElectionType>) getHibernateTemplate().findByCriteria(criteria, 0, maxResults);
    }

    public List<Party> findParties() {
        return (List<Party>) getHibernateTemplate().find("from Party this");
    }

    public List<Party> findParties(ElectionType electionType) {
        String hql = "from Party this where this.electionType = ? order by this.stall asc";
        return getHibernateTemplate().find(hql, electionType);
    }

    public List<Candidate> findCandidates() {
        return (List<Candidate>) getHibernateTemplate().find("from Candidate this");
    }

    public List<Candidate> findCandidates(Party party) {
        DetachedCriteria criteria = DetachedCriteria.forClass(Candidate.class);
        criteria.add(Expression.eq("party", party));
        return (List<Candidate>) getHibernateTemplate().findByCriteria(criteria, 0, maxResults);
    }

    public List<Candidate> findCandidates(Year year, Party party) {
        DetachedCriteria criteria = DetachedCriteria.forClass(Candidate.class);
        criteria.add(Expression.eq("year", year));
        criteria.add(Expression.eq("party", party));
        return (List<Candidate>) getHibernateTemplate().findByCriteria(criteria, 0, maxResults);
    }

    public List<Position> findPositions() {
        return (List<Position>) getHibernateTemplate().find("from Position this");
    }

    public List<State> findStates() {
        return (List<State>) getHibernateTemplate().find("from State this");
    }

    public List<State> findStates(String name) {
        name = name.concat("%");
        return (List<State>) getHibernateTemplate().find("from State this where upper(this.name) like upper(?)", name);
    }

    public State findState(String name) {
        name = name.concat("%");
        return (State) unique(getHibernateTemplate().find("from State this where upper(this.name) like upper(?)", name));
    }

    public List<Town> findTowns() {
        return (List<Town>) getHibernateTemplate().find("from Town this join fetch this.state");
    }

    public List<Town> findTowns(boolean tracked) {
        return (List<Town>) getHibernateTemplate().find("from Town this join fetch this.state where this.tracked = ?", tracked);
    }

    public List<String> findTownNames() {
        return (List<String>) getHibernateTemplate().find("select this.name from Town this");
    }

    public List<Town> findTowns(String name, State state) {
        name = name.concat("%");
        return (List<Town>) getHibernateTemplate().find("from Town this where upper(this.name) like upper(?) and this.state = ?", new Object[]{name, state});
    }

    public List<Town> findTowns(State state) {
        DetachedCriteria criteria = DetachedCriteria.forClass(Town.class);
        criteria.add(Expression.eq("state", state));
        return (List<Town>) getHibernateTemplate().findByCriteria(criteria, 0, maxResults);
    }

    public List<Town> findTowns(String name) {
        name = name.concat("%");
        return (List<Town>) getHibernateTemplate().find("from Town this where upper(this.name) like upper(?)", name);
    }

    public List<BallotCentre> findBallotCentres() {
        return (List<BallotCentre>) getHibernateTemplate().find("from BallotCentre this");
    }

    public List<BallotCentre> findBallotCentres(String name, Town town) {
        name = name.concat("%");
        return (List<BallotCentre>) getHibernateTemplate().find("from BallotCentre this where upper(this.description) like upper(?) and this.town = ?", new Object[]{name, town});
    }

    public List<BallotCentre> findBallotCentres(Town town) {
        List args = new ArrayList();

        StringBuilder hql = new StringBuilder("from BallotCentre this join fetch this.town town join fetch town.state state join fetch this.year year where 1 = 1 ");

        if (town != null) {
            hql.append(" and town.id = ? ");
            args.add(town.getId());
        }

        hql.append(" and this.year.active = true");

        hql.append(" order by this.description");

        return (List<BallotCentre>) find(hql.toString(), args.toArray(), -1);
    }

    public List<String> findBallotCentreDescriptions(Town town) {
        List args = new ArrayList();

        StringBuilder hql = new StringBuilder("select this.description from BallotCentre this where 1 = 1 ");

        if (town != null) {
            hql.append(" and this.town.id = ? ");
            args.add(town.getId());
        }

        hql.append(" order by this.description");

        return (List<String>) find(hql.toString(), args.toArray(), -1);
    }

    public List<BallotCentre> findBallotCentres(String name) {
        name = name.concat("%");
        return (List<BallotCentre>) getHibernateTemplate().find("from BallotCentre this where upper(this.description) like upper(?)", name);
    }

    public List<Jrv> findJrvs() {
        return (List<Jrv>) getHibernateTemplate().find("from Jrv this");
    }

    public List<Jrv> findJrvs(String code, BallotCentre ballot) {
        code = "%".concat(code.concat("%"));
        return (List<Jrv>) getHibernateTemplate().find("from Jrv this join fetch this.ballotCentre ballot join fetch ballot.town town where upper(this.code) like upper(?) and this.ballotCentre = ? order by this.code asc", new Object[]{code, ballot});
    }

    public List<Jrv> findJrvs(BallotCentre ballotCentre) {
        DetachedCriteria criteria = DetachedCriteria.forClass(Jrv.class);
        criteria.add(Expression.eq("ballotCentre", ballotCentre));
        return (List<Jrv>) getHibernateTemplate().findByCriteria(criteria);
    }

    public List<Jrv> findJrvs(String code) {
        code = code.concat("%");
        return (List<Jrv>) getHibernateTemplate().find("from Jrv this where code like ? order by this.code asc", code);
    }

    public List<Jrv> findJrvs(String code, Town town, User user) {
        code = "%".concat(code.concat("%"));
        String hql = "select j.* "
                + "  from vce_jrvs j, vce_ballot_centre b, vce_towns t, vce_user_towns ut "
                + " where j.ballot_centre_id = b.id "
                + "   and b.town_id = t.id "
                + "   and t.id = ut.town_id "
                + "   and t.id = ? "
                + "   and ut.user_id = ? "
                + "   and j.code like ?"
                + " order by j.code asc";
        return (List<Jrv>) findSql(hql, Jrv.class, new Object[]{town.getId(), user.getId(), code}, 0);
    }

    public List<Jrv> findJrvs(String code, Town town, BallotCentre ballot, Counting counting, Round round, ElectionType electionType) {
        code = code.concat("%");
        List args = new ArrayList();

        StringBuilder buff = new StringBuilder("from Jrv this left join fetch this.ballotCentre ballot join fetch ballot.town town where upper(this.code) like upper(?)");
        args.add(code);

        if (ballot != null) {
            buff.append(" and this.ballotCentre = ? ");
            args.add(ballot);
        }

        if (town != null) {
            buff.append(" and ballot.town = ? ");
            args.add(town);
        }

        buff.append(" and this not in (select doc.jrv from Document doc where doc.counting = ? and doc.round = ? and doc.electionType = ?)");
        
        args.add(counting);
        args.add(round);
        args.add(electionType);
        buff.append(" order by this.code");
        return (List<Jrv>) getHibernateTemplate().find(buff.toString(), args.toArray());
    }

    public List<Jrv> findRegisteredJrvs(String code, Town town, BallotCentre ballot, Counting counting, Round round, ElectionType electionType) {
        code = "%".concat(code.concat("%"));
        List args = new ArrayList();

        StringBuilder buff = new StringBuilder("from Jrv this join fetch this.ballotCentre ballot join fetch ballot.town town where upper(this.code) like upper(?)");
        args.add(code);

        if (ballot != null) {
            buff.append(" and this.ballotCentre = ? ");
            args.add(ballot);
        }

        if (town != null) {
            buff.append(" and ballot.town = ? ");
            args.add(town);
        }

        buff.append(" and this in (select doc.jrv from Document doc where doc.counting = ? and doc.round = ? and doc.electionType = ?)");
        args.add(counting);
        args.add(round);
        args.add(electionType);
        buff.append(" order by this.code");
        return (List<Jrv>) getHibernateTemplate().find(buff.toString(), args.toArray());
    }

    public List<Jrv> findJrvs(String code, User user, Counting counting, Round round) {
        code = "%".concat(code.concat("%"));
        String countString = counting.toString();
        String roundString = round.toString();

        String hql = "select j.* "
                + "  from vce_jrvs j, vce_ballot_centre b, vce_towns t, vce_user_towns ut "
                + " where j.ballot_centre_id = b.id "
                + "   and b.town_id = t.id "
                + "   and t.id = ut.town_id "
                + "   and ut.user_id = ? "
                + "   and j.code like ? "
                + "   and j.id not in "
                + "   (select d.jrv_id from vce_documents d "
                + "     where d.counting = ? and d.round = ?)"
                + " order by j.code asc";
        return (List<Jrv>) findSql(hql, Jrv.class, new Object[]{user.getId(), code, countString, roundString}, 0);
    }

    public List<Jrv> findInsertedJrvs(String code, User user, Counting counting, Round round) {
        code = "%".concat(code.concat("%"));
        String countString = counting.toString();
        String roundString = round.toString();

        String hql = "select j.* "
                + "  from vce_jrvs j, vce_ballot_centre b, vce_towns t, vce_user_towns ut "
                + " where j.ballot_centre_id = b.id "
                + "   and b.town_id = t.id "
                + "   and t.id = ut.town_id "
                + "   and ut.user_id = ? "
                + "   and j.code like ? "
                + "   and j.id in "
                + "   (select d.jrv_id from vce_documents d "
                + "     where d.counting = ? and d.round = ?)"
                + " order by j.code asc";
        return (List<Jrv>) findSql(hql, Jrv.class, new Object[]{user.getId(), code, countString, roundString}, 0);
    }

    public List<Document> findDocuments() {
        return (List<Document>) getHibernateTemplate().find("from Document this");
    }

    public List<Vote> findVotes() {
        return (List<Vote>) getHibernateTemplate().find("from Vote this");
    }

    public List<Vote> findVotes(Document document) {
        DetachedCriteria criteria = DetachedCriteria.forClass(Vote.class);
        criteria.add(Expression.eq("document", document));
        return (List<Vote>) getHibernateTemplate().findByCriteria(criteria, 0, maxResults);
    }

    public void storeYear(Year year) {
        getHibernateTemplate().saveOrUpdate(year);
    }

    public void storeUser(User user) {
        getHibernateTemplate().saveOrUpdate(user);
    }

    public void storeElectionType(ElectionType electionType) {
        getHibernateTemplate().saveOrUpdate(electionType);
    }

    public void storeParty(Party party) {
        getHibernateTemplate().saveOrUpdate(party);
    }

    public void storeCandidate(Candidate candidate) {
        getHibernateTemplate().saveOrUpdate(candidate);
    }

    public void storePosition(Position position) {
        getHibernateTemplate().saveOrUpdate(position);
    }

    public void storeState(State state) {
        getHibernateTemplate().saveOrUpdate(state);
    }

    public void storeTown(Town town) {
        getHibernateTemplate().saveOrUpdate(town);
    }

    public void storeBallotCentre(BallotCentre ballotCentre) {
        getHibernateTemplate().saveOrUpdate(ballotCentre);
    }

    public void storeJrv(Jrv jrv) {
        getHibernateTemplate().saveOrUpdate(jrv);
    }

    public void storeDocument(Document document) {
        getHibernateTemplate().saveOrUpdate(document);
    }

    public void storeVote(Vote vote) {
        getHibernateTemplate().saveOrUpdate(vote);
    }

    public void storeFiscal(Fiscal fiscal) {
        getHibernateTemplate().saveOrUpdate(fiscal);
    }

    public void storeImpugnment(Impugnment impugnment) {
        getHibernateTemplate().saveOrUpdate(impugnment);
    }

    public Document getlastInsertedDocument(User user, Counting counting, ElectionType electionType) {
        String hql = "from Document this join fetch this.audit.createdBy user join fetch this.electionType electionType join fetch this.jrv where user = ? and this.counting = ?  and electionType = ? order by this.id desc";
        List<Document> results = getHibernateTemplate().find(hql, new Object[]{user, counting, electionType});
        if (results.isEmpty()) {
            return null;
        } else {
            return results.get(0);
        }
    }

    public int getTotalInsertedDocuments(User user, Counting counting, ElectionType electionType) {
        String hql = "from Document this join fetch this.audit.createdBy user join fetch this.electionType electionType where user = ? and this.counting = ? and electionType = ? order by this.id desc";
        List<Document> results = getHibernateTemplate().find(hql, new Object[]{user, counting, electionType});
        if (results.isEmpty()) {
            return 0;
        } else {
            return results.size();
        }
    }

    public List find(final String hql, final Object[] args, final int maxResults) {
        HibernateCallback callback = new HibernateCallback() {

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                if (maxResults > 0) {
                    query.setMaxResults(maxResults);
                }
                if (args != null) {
                    for (int i = 0; i < args.length; i++) {
                        query.setParameter(i, args[i]);
                    }
                }
                return query.list();
            }
        };
        return (List) getHibernateTemplate().execute(callback);
    }

    public List findSql(final String hql, final Class clazz, final Object[] args, final int maxResults) {
        HibernateCallback callback = new HibernateCallback() {

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                org.hibernate.SQLQuery query = session.createSQLQuery(hql);
                if (clazz != null) {
                    query.addEntity(clazz);
                }
                if (args != null) {
                    for (int i = 0; i < args.length; i++) {
                        query.setParameter(i, args[i]);
                    }
                }
                if (maxResults > 0) {
                    query.setMaxResults(maxResults);
                }
                return query.list();
            }
        };
        return (List) getHibernateTemplate().execute(callback);
    }

    public Document getDocument(Jrv jrv, ElectionType electionType, Counting counting, Round round) {
        String hql = "from Document this left join fetch this.jrv jrv join fetch this.votes where jrv = ? and this.counting = ? and this.round = ? and this.electionType = ?";
        return (Document) unique(getHibernateTemplate().find(hql, new Object[]{jrv, counting, round, electionType}));
    }
    
    public Document getDocument(BallotCentre ballotCentre, Counting counting, Round round) {
        String hql = "from Document this left join fetch this.ballotCentre ballotCentre join fetch this.votes where ballotCentre = ? and this.counting = ? and this.round = ?";
        return (Document) unique(getHibernateTemplate().find(hql, new Object[]{ballotCentre, counting, round}));
    }

    public List<Padron> findElectors(String cedula) {
        cedula = cedula.toUpperCase();

        String hql = "from Padron this where this.cedula = ?";
        List<Padron> results = getHibernateTemplate().find(hql, cedula);

        if (results.isEmpty()) {
            return null;
        } else {
            return results;
        }
    }

    public List<Object[]> findResults(Jrv jrv, Counting counting) throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Object[]> results = new ArrayList<Object[]>();
        System.out.println("Entra procedimiento");

        try {
            cn = dataSource.getConnection();
            cn.setAutoCommit(false);

            String sql = "select town_id, registral, town_name, ballot_id, ballot_code, ballot_name, doc_id, jrv_id, code, counting, plc, fsln, aln, apre, apli, nulos, validos from vce_results where jrv_id = ? and counting = ?";
            pst = cn.prepareStatement(sql);

            pst.setLong(1, jrv.getId());
            pst.setString(2, counting.getName());

            rs = pst.executeQuery();

            if (rs.next()) {
                System.out.println("Execute query: " + rs.getLong("town_id"));
                Object[] object = new Object[17];

                object[0] = rs.getLong("town_id");
                object[1] = rs.getLong("registral");
                object[2] = rs.getString("town_name");
                object[3] = rs.getLong("ballot_id");
                object[4] = rs.getLong("ballot_code");
                object[5] = rs.getString("ballot_name");
                object[6] = rs.getLong("doc_id");
                object[7] = rs.getLong("jrv_id");
                object[8] = rs.getString("code");
                object[9] = rs.getString("counting");
                object[10] = rs.getLong("plc");
                object[11] = rs.getLong("fsln");
                object[12] = rs.getLong("aln");
                object[13] = rs.getLong("apre");
                object[14] = rs.getLong("apli");
                object[15] = rs.getLong("nulos");
                object[16] = rs.getLong("validos");
                results.add(object);
            } else {
                System.out.println("Returning Null");
                results = null;
            }

            if (results != null && results.size() > 0) {
                System.out.println("Size: " + results.size());
                return results;
            } else {
                return null;
            }
        } finally {
            rs.close();
            pst.close();
            cn.rollback();
            cn.close();
            cn = null;
        }
    }

    public List<RegisteredFiscal> findRegisteredFiscals(String name) {
        List args = new ArrayList();

        StringBuilder buff = new StringBuilder("from RegisteredFiscal this where 1 = 1");

        if (!isBlank(name)) {
            name = "%".concat(name.toUpperCase().concat("%"));
            buff.append(" and upper(this.fullName) like ? ");
            args.add(name);
        }

        buff.append(" order by this.fullName");
        return (List<RegisteredFiscal>) getHibernateTemplate().find(buff.toString(), args.toArray());
    }
}
